{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "adjust_data_file = \"../附件四：354个操作变量信息.xlsx\"  # 模型后期优化的依据 \n",
    "sample_data_file = \"../附件一：325个样本数据.xlsx\"  # 样本数据\n",
    "origin_data_file = \"../附件三：285号和313号样本原始数据.xlsx\"  # 原始数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>S-ZORB.CAL_H2.PV</th>\n",
       "      <th>S-ZORB.PDI_2102.PV</th>\n",
       "      <th>S-ZORB.PT_2801.PV</th>\n",
       "      <th>S-ZORB.FC_2801.PV</th>\n",
       "      <th>S-ZORB.TE_2103.PV</th>\n",
       "      <th>S-ZORB.TE_2005.PV</th>\n",
       "      <th>S-ZORB.PT_2101.PV</th>\n",
       "      <th>S-ZORB.PDT_2104.PV</th>\n",
       "      <th>S-ZORB.SIS_PDT_2103B.PV</th>\n",
       "      <th>S-ZORB.TC_2101.PV</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>氢油比</th>\n",
       "      <th>反应过滤器压差</th>\n",
       "      <th>还原器压力</th>\n",
       "      <th>还原器流化氢气流量</th>\n",
       "      <th>反应器上部温度</th>\n",
       "      <th>反应器底部温度</th>\n",
       "      <th>反应器顶部压力</th>\n",
       "      <th>反应器顶底压差</th>\n",
       "      <th>反应过滤器压差</th>\n",
       "      <th>反吹氢气温度</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.273498</td>\n",
       "      <td>24.17817</td>\n",
       "      <td>2.528462</td>\n",
       "      <td>856.2485</td>\n",
       "      <td>421.5055</td>\n",
       "      <td>421.1933</td>\n",
       "      <td>2.426675</td>\n",
       "      <td>59.71847</td>\n",
       "      <td>1108.377</td>\n",
       "      <td>244.1175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.273493</td>\n",
       "      <td>24.17971</td>\n",
       "      <td>2.528483</td>\n",
       "      <td>856.2297</td>\n",
       "      <td>421.5057</td>\n",
       "      <td>421.1934</td>\n",
       "      <td>2.426696</td>\n",
       "      <td>59.71767</td>\n",
       "      <td>1108.372</td>\n",
       "      <td>244.1178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.273488</td>\n",
       "      <td>24.18125</td>\n",
       "      <td>2.528504</td>\n",
       "      <td>856.2109</td>\n",
       "      <td>421.5059</td>\n",
       "      <td>421.1936</td>\n",
       "      <td>2.426718</td>\n",
       "      <td>59.71688</td>\n",
       "      <td>1108.368</td>\n",
       "      <td>244.1180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.273483</td>\n",
       "      <td>24.18279</td>\n",
       "      <td>2.528525</td>\n",
       "      <td>856.1922</td>\n",
       "      <td>421.5061</td>\n",
       "      <td>421.1937</td>\n",
       "      <td>2.426739</td>\n",
       "      <td>59.71609</td>\n",
       "      <td>1108.363</td>\n",
       "      <td>244.1182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.273478</td>\n",
       "      <td>24.18434</td>\n",
       "      <td>2.528546</td>\n",
       "      <td>856.1734</td>\n",
       "      <td>421.5063</td>\n",
       "      <td>421.1939</td>\n",
       "      <td>2.426760</td>\n",
       "      <td>59.71530</td>\n",
       "      <td>1108.358</td>\n",
       "      <td>244.1184</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  S-ZORB.CAL_H2.PV S-ZORB.PDI_2102.PV S-ZORB.PT_2801.PV S-ZORB.FC_2801.PV  \\\n",
       "               氢油比            反应过滤器压差             还原器压力         还原器流化氢气流量   \n",
       "0         0.273498           24.17817          2.528462          856.2485   \n",
       "1         0.273493           24.17971          2.528483          856.2297   \n",
       "2         0.273488           24.18125          2.528504          856.2109   \n",
       "3         0.273483           24.18279          2.528525          856.1922   \n",
       "4         0.273478           24.18434          2.528546          856.1734   \n",
       "\n",
       "  S-ZORB.TE_2103.PV S-ZORB.TE_2005.PV S-ZORB.PT_2101.PV S-ZORB.PDT_2104.PV  \\\n",
       "            反应器上部温度           反应器底部温度           反应器顶部压力            反应器顶底压差   \n",
       "0          421.5055          421.1933          2.426675           59.71847   \n",
       "1          421.5057          421.1934          2.426696           59.71767   \n",
       "2          421.5059          421.1936          2.426718           59.71688   \n",
       "3          421.5061          421.1937          2.426739           59.71609   \n",
       "4          421.5063          421.1939          2.426760           59.71530   \n",
       "\n",
       "  S-ZORB.SIS_PDT_2103B.PV S-ZORB.TC_2101.PV  \n",
       "                  反应过滤器压差            反吹氢气温度  \n",
       "0                1108.377          244.1175  \n",
       "1                1108.372          244.1178  \n",
       "2                1108.368          244.1180  \n",
       "3                1108.363          244.1182  \n",
       "4                1108.358          244.1184  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "origin_data = pd.read_excel(io=origin_data_file, sheet_name=\"操作变量\", header=[0, 1], skiprows=[0])\n",
    "# 查看 原始数据情况\n",
    "origin_data.iloc[:5, 1:11]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>S-ZORB.CAL_H2.PV</th>\n",
       "      <th>S-ZORB.PDI_2102.PV</th>\n",
       "      <th>S-ZORB.PT_2801.PV</th>\n",
       "      <th>S-ZORB.FC_2801.PV</th>\n",
       "      <th>S-ZORB.TE_2103.PV</th>\n",
       "      <th>S-ZORB.TE_2005.PV</th>\n",
       "      <th>S-ZORB.PT_2101.PV</th>\n",
       "      <th>S-ZORB.PDT_2104.PV</th>\n",
       "      <th>S-ZORB.SIS_PDT_2103B.PV</th>\n",
       "      <th>S-ZORB.TC_2101.PV</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>氢油比</th>\n",
       "      <th>反应过滤器压差</th>\n",
       "      <th>还原器压力</th>\n",
       "      <th>还原器流化氢气流量</th>\n",
       "      <th>反应器上部温度</th>\n",
       "      <th>反应器底部温度</th>\n",
       "      <th>反应器顶部压力</th>\n",
       "      <th>反应器顶底压差</th>\n",
       "      <th>反应过滤器压差</th>\n",
       "      <th>反吹氢气温度</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.276318</td>\n",
       "      <td>31.211968</td>\n",
       "      <td>2.526857</td>\n",
       "      <td>647.752380</td>\n",
       "      <td>424.615900</td>\n",
       "      <td>421.608145</td>\n",
       "      <td>2.393991</td>\n",
       "      <td>101.290580</td>\n",
       "      <td>33.480159</td>\n",
       "      <td>240.088445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.277127</td>\n",
       "      <td>31.461969</td>\n",
       "      <td>2.527411</td>\n",
       "      <td>651.821920</td>\n",
       "      <td>420.953385</td>\n",
       "      <td>417.927830</td>\n",
       "      <td>2.394113</td>\n",
       "      <td>101.544925</td>\n",
       "      <td>33.324023</td>\n",
       "      <td>239.809820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.277013</td>\n",
       "      <td>31.339998</td>\n",
       "      <td>2.528865</td>\n",
       "      <td>650.747150</td>\n",
       "      <td>423.941065</td>\n",
       "      <td>420.003515</td>\n",
       "      <td>2.394545</td>\n",
       "      <td>101.618320</td>\n",
       "      <td>33.251831</td>\n",
       "      <td>239.742695</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.277222</td>\n",
       "      <td>31.033443</td>\n",
       "      <td>2.530589</td>\n",
       "      <td>652.669055</td>\n",
       "      <td>423.722805</td>\n",
       "      <td>419.964775</td>\n",
       "      <td>2.392854</td>\n",
       "      <td>105.029760</td>\n",
       "      <td>33.197268</td>\n",
       "      <td>239.732465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.277310</td>\n",
       "      <td>30.826894</td>\n",
       "      <td>2.530938</td>\n",
       "      <td>649.340920</td>\n",
       "      <td>427.132000</td>\n",
       "      <td>422.128935</td>\n",
       "      <td>2.392715</td>\n",
       "      <td>105.937455</td>\n",
       "      <td>32.785944</td>\n",
       "      <td>239.835635</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  S-ZORB.CAL_H2.PV S-ZORB.PDI_2102.PV S-ZORB.PT_2801.PV S-ZORB.FC_2801.PV  \\\n",
       "               氢油比            反应过滤器压差             还原器压力         还原器流化氢气流量   \n",
       "0         0.276318          31.211968          2.526857        647.752380   \n",
       "1         0.277127          31.461969          2.527411        651.821920   \n",
       "2         0.277013          31.339998          2.528865        650.747150   \n",
       "3         0.277222          31.033443          2.530589        652.669055   \n",
       "4         0.277310          30.826894          2.530938        649.340920   \n",
       "\n",
       "  S-ZORB.TE_2103.PV S-ZORB.TE_2005.PV S-ZORB.PT_2101.PV S-ZORB.PDT_2104.PV  \\\n",
       "            反应器上部温度           反应器底部温度           反应器顶部压力            反应器顶底压差   \n",
       "0        424.615900        421.608145          2.393991         101.290580   \n",
       "1        420.953385        417.927830          2.394113         101.544925   \n",
       "2        423.941065        420.003515          2.394545         101.618320   \n",
       "3        423.722805        419.964775          2.392854         105.029760   \n",
       "4        427.132000        422.128935          2.392715         105.937455   \n",
       "\n",
       "  S-ZORB.SIS_PDT_2103B.PV S-ZORB.TC_2101.PV  \n",
       "                  反应过滤器压差            反吹氢气温度  \n",
       "0               33.480159        240.088445  \n",
       "1               33.324023        239.809820  \n",
       "2               33.251831        239.742695  \n",
       "3               33.197268        239.732465  \n",
       "4               32.785944        239.835635  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "samples_data = pd.read_excel(io=sample_data_file, header=[0, 1], skiprows=[0])\n",
    "# 查看 样本数据情况\n",
    "samples_data.iloc[:5, 16:26]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>位号</th>\n",
       "      <th>中文名称</th>\n",
       "      <th>单位</th>\n",
       "      <th>Δ值</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S-ZORB.CAL_H2.PV</td>\n",
       "      <td>氢油比</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S-ZORB.PDI_2102.PV</td>\n",
       "      <td>反应过滤器压差</td>\n",
       "      <td>KPa</td>\n",
       "      <td>1.00</td>\n",
       "      <td>10</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S-ZORB.PT_2801.PV</td>\n",
       "      <td>还原器压力</td>\n",
       "      <td>MPa</td>\n",
       "      <td>0.10</td>\n",
       "      <td>2.35</td>\n",
       "      <td>2.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S-ZORB.FC_2801.PV</td>\n",
       "      <td>还原器流化氢气流量</td>\n",
       "      <td>Nm3/h</td>\n",
       "      <td>50.00</td>\n",
       "      <td>600</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S-ZORB.TE_2103.PV</td>\n",
       "      <td>反应器上部温度</td>\n",
       "      <td>℃</td>\n",
       "      <td>1.00</td>\n",
       "      <td>410</td>\n",
       "      <td>435</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   位号       中文名称     单位     Δ值   min   max\n",
       "0    S-ZORB.CAL_H2.PV        氢油比    NaN   0.01  0.20  0.37\n",
       "1  S-ZORB.PDI_2102.PV    反应过滤器压差    KPa   1.00    10    35\n",
       "2   S-ZORB.PT_2801.PV      还原器压力    MPa   0.10  2.35  2.70\n",
       "3   S-ZORB.FC_2801.PV  还原器流化氢气流量  Nm3/h  50.00   600  1000\n",
       "4   S-ZORB.TE_2103.PV    反应器上部温度      ℃   1.00   410   435"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adjust_data = pd.read_excel(io=adjust_data_file, header=[0])\n",
    "adjust_data['min'] = adjust_data['取值范围'].map(lambda x:x.split('-')[0])\n",
    "adjust_data['max'] = adjust_data['取值范围'].map(lambda x:x.split('-')[1])\n",
    "adjust_data = adjust_data.drop('取值范围', axis=1)\n",
    "adjust_data = adjust_data.drop('变量编号', axis=1)\n",
    "adjust_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Stored 'origin_data' (DataFrame)\n",
      "Stored 'samples_data' (DataFrame)\n",
      "Stored 'adjust_data' (DataFrame)\n"
     ]
    }
   ],
   "source": [
    "%store origin_data\n",
    "%store samples_data\n",
    "%store adjust_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python(chenhao)",
   "language": "python",
   "name": "venv"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
